package com.southwind.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCTools<T> {

    /**
     * 可以传SQL参数返回数据集合
     * @param connection
     * @param sql
     * @param clazz
     * @param params
     * @return
     */
    public List<T> getBeans(Connection connection,String sql,Class clazz,Object... params){
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            //查询SQL
            statement = connection.prepareStatement(sql);
            //替换参数
            fillParams(statement, params);
            //获取结果集
            resultSet = statement.executeQuery();
            //解析结果集
            parseDataForList(resultSet, clazz, list);
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return list;
    }

    /**
     * 查询对象集合
     * @param connection
     * @param sql
     * @param clazz
     * @return
     */
    public List<T> getBeans(Connection connection,String sql,Class clazz){
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            //查询SQL
            statement = connection.prepareStatement(sql);
            //获取结果集
            resultSet = statement.executeQuery();
            //解析结果集
            parseDataForList(resultSet, clazz, list);
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return list;
    }

    /**
     * 可以传SQL参数查询某个对象
     * @param connection
     * @param sql
     * @param clazz
     * @param params
     * @return
     */
    public T getBean(Connection connection,String sql,Class clazz,Object... params){
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //查询SQL
            statement = connection.prepareStatement(sql);
            //替换参数
            fillParams(statement, params);
            //获取结果集
            resultSet = statement.executeQuery();
            //映射成JavaBean，反射
            Object object = clazz.getConstructor(null).newInstance(null);
            //解析结果集
            parseDataForBean(resultSet, object);
            return (T)object;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return null;
    }

    /**
     * 通过SQL查询某个对象
     * @param connection
     * @param sql
     * @param clazz
     * @return
     */
    public T getBean(Connection connection, String sql, Class clazz){
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //查询SQL
            statement = connection.prepareStatement(sql);
            //获取结果集
            resultSet = statement.executeQuery();
            //映射成JavaBean，反射
            Object object = clazz.getConstructor(null).newInstance(null);
            //解析结果集
            parseDataForBean(resultSet, object);
            return (T)object;
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            C3P0Tools.release(connection, statement, resultSet);
        }
        return null;
    }

    /**
     * 填充SQL参数
     * @param statement
     * @param params
     */
    public void fillParams(PreparedStatement statement,Object... params){
        try {
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                String typeName = param.getClass().getTypeName();
                switch (typeName){
                    case "java.lang.Integer":
                        statement.setInt(i+1,(Integer) param);
                        break;
                    case "java.lang.String":
                        statement.setString(i+1,(String) param);
                        break;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 解析结果集，返回数据集合
     * @param resultSet
     * @param clazz
     * @param list
     */
    public void parseDataForList(ResultSet resultSet,Class clazz,List<T> list){
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                //映射成JavaBean，反射
                Object object = clazz.getConstructor(null).newInstance(null);
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnTypeName = metaData.getColumnTypeName(i);
                    Object value = null;
                    switch (columnTypeName){
                        case "INT":
                            value = resultSet.getInt(columnName);
                            break;
                        case "VARCHAR":
                            value = resultSet.getString(columnName);
                            break;
                    }
                    //给属性赋值
                    //获取setter方法
                    String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1);
                    Field field = clazz.getDeclaredField(columnName);
                    Method method = clazz.getMethod(methodName, field.getType());
                    method.invoke(object,value);
                }
                list.add((T)object);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析结果集，给对象赋值
     * @param resultSet
     * @param object
     */
    public void parseDataForBean(ResultSet resultSet,Object object){
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnTypeName = metaData.getColumnTypeName(i);
                    Object value = null;
                    switch (columnTypeName){
                        case "INT":
                            value = resultSet.getInt(columnName);
                            break;
                        case "VARCHAR":
                            value = resultSet.getString(columnName);
                            break;
                    }
                    //给属性赋值
                    //获取setter方法
                    String methodName = "set"+columnName.substring(0, 1).toUpperCase()+columnName.substring(1);
                    Field field = object.getClass().getDeclaredField(columnName);
                    Method method = object.getClass().getMethod(methodName, field.getType());
                    method.invoke(object,value);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e){
            e.printStackTrace();
        } catch (SecurityException e){
            e.printStackTrace();
        } catch (NoSuchMethodException e){
            e.printStackTrace();
        }
    }

}
